# coding=utf-8

import web
from web.utils import storage

import utils
from db import connection
from sc import SC
from __init__ import UserSession

db = connection.default

def get_uid_by_email(email):
    """获取给定邮箱的注册用户内码"""
    sets = db.select('user_auth'
            , what = 'id'
            , where = 'email = $email'
            , vars = locals())
    return sets and sets[0].id or 0

def unsubscribe_by_email(email):
    """退订给定邮箱"""
    # 1) 删除 user_subscribe记录
    # 2) 删除 user_subscribe_queue记录
    with db.transaction():
        db.delete('user_subscribe'
                , where = 'email = $email'
                , vars = locals())
        db.delete('user_subscribe_queue'
                , where = 'sign = $sign'
                , vars = { 'sign': utils.md5(email) })
        return True
    return False



def add_empty_user(email):
    """添加一个空用户"""
    with db.transaction():
        uid = db.insert('user_info'
                , name = email.split('@')[0]
                , date_created = web.SQLLiteral('NOW()'))
        if uid > 0:
            db.insert('user_auth'
                    , id = uid
                    , email = email)
            # 返回新增的用户内码
            return uid

    # 添加失败
    return False

def reset_pwd_by_email(email, pwd_encoded):
    """根据用户邮箱重置其密码"""
    with db.transaction():
        db.update('user_auth'
            , passwd = pwd_encoded
            , where = 'email = $email'
            , vars = locals())
        # 获取这个email关联的订阅信息
        # 是否关联了用户
        sets = db.select('user_subscribe'
                , what = 'id,uid'
                , where = 'email = $email'
                , vars = locals())
        usr = sets and sets[0] or None
        if usr and usr.uid <= 0:
            # 未关联到用户
            # 这里进行一次关联
            db.query('''
                    UPDATE user_subscribe 
                    SET uid = (SELECT id
                               FROM user_auth
                               WHERE email = $email)
                    WHERE id = $id;
                    '''
                    , vars = {
                            'id': usr.id,
                            'email': email,
                        })
        return True
    return False
        

def save_login(usr, remeber):
    """记录登录状态"""
    UserSession.save_login_cookie(None
            , {'id': usr.id, 'screen_name': usr.name}
            , None)

def login(email, pwd, remeber = True, callback = save_login):
    """
    用户登录逻辑
    """
    usr = get_login_by_email(email)
    if usr:
        # 帐号有效
        if utils.cmp_i(utils.md5(pwd), usr.passwd) == 0:
            # 密码正确
            # 更新最后登录时间
            db.update('user_info'
                    , last_login = web.SQLLiteral('NOW()')
                    , where = 'id = $uid'
                    , vars = {
                            'uid': usr.id
                        })
            # 调用回调函数
            callback(usr, remeber)
            # 返回成功标识
            return True

    return False

def login_bind(usr, cook):
    """
    登录绑定
    """
    if cook.user and cook.token:
        # 添加user_bind信息
        with db.transaction():
            db.query('''INSERT IGNORE INTO user_bind 
                        SET uid = $uid,bind_type = $bind_type,
                            bind_id = $bind_id,bind_name = $bind_name,
                            bind_avatar = $bind_avatar,bind_token = $bind_token;
                    '''
                    , vars = {
                            'uid': usr.id,
                            'bind_type': cook.token['type'],
                            'bind_id': cook.user['uid'],
                            'bind_name': cook.user['screen_name'],
                            'bind_avatar': cook.user['avatar'],
                            'bind_token': utils.json_encode(cook.token)
                        })
            # 更新最后登录时间
            db.update('user_info'
                    , name = cook.user['screen_name']
                    , last_login = web.SQLLiteral('NOW()')
                    , where = 'id = $uid'
                    , vars = {
                            'uid': usr.id
                        })

        cook.user['id'] = usr.id
        # 保存cookie
        UserSession.save_login_cookie(cook.token['type']
                , storage(cook.user)
                , storage(cook.token))
        # 返回登录用户
        return storage(id = usr.id, screen_name = cook.user['screen_name'])

    # 添加失败
    return False 

def register_bind(form, cook):
    """
    注册并绑定
    """
    if cook.user and cook.token:
        # 添加一条记录
        with db.transaction():
            uid = db.insert('user_info'
                    , name = cook.user['screen_name']
                    , date_created = web.SQLLiteral('NOW()')
                    , last_login = web.SQLLiteral('NOW()'))
            if uid:
                db.insert('user_bind'
                        , uid = uid
                        , bind_type = cook.token['type']
                        , bind_id = cook.user['uid']
                        , bind_name = cook.user['screen_name']
                        , bind_avatar = cook.user['avatar']
                        , bind_token = utils.json_encode(cook.token))
                db.insert('user_auth'
                        , id = uid
                        , email = form.email
                        , passwd = utils.md5(form.pswd))
        if uid:
            # 添加成功
            cook.user['id'] = uid
            # 保存cookie
            UserSession.save_login_cookie(cook.token['type']
                    , storage(cook.user)
                    , storage(cook.token))
            # 返回注册用户
            return storage(id = uid, screen_name = cook.user['screen_name'])
    # 添加失败
    return False 

def exists_email(email):
    """获取给定email是否已经注册"""
    sets = db.query('''SELECT EXISTS(SELECT NULL 
                                     FROM user_auth 
                                     WHERE email = $email) AS e'''
                    , vars = locals())
    return sets[0].e == 1

def get_login_by_email(email):
    """
    获取给定email的登录信息
    """
    sets = db.query('''
            SELECT ui.id,ui.name,ua.passwd
            FROM user_auth AS ua
                INNER JOIN
                user_info AS ui
                    ON ui.id = ua.id
            WHERE ua.email = $email;
            '''
            , vars = locals())
    return sets and sets[0] or None

def active_subscribe(req):
    """
    确认订阅
    @req as object, 表示一条订阅请求记录
    """
    if req:
        with db.transaction():
            # 更新队列状态
            if db.update('user_subscribe_queue'
                    , status = 1
                    , where = 'id = $id'
                    , vars = {'id':req.id}):
                sets = db.select('user_subscribe'
                        , what = 'id'
                        , where = 'email = $email'
                        , vars = {
                                'email': req.email
                            })
                if sets:
                    return db.update('user_subscribe'
                            , uid = req.uid
                            , where = 'id = $id'
                            , vars = {
                                    'id': sets[0].id
                                })
                else:
                    return db.insert('user_subscribe'
                            , uid = req.uid
                            , email = req.email
                            , depart_id = req.depart_id
                            , date_created = web.SQLLiteral('NOW()'));
                
    return False

def get_subscribe_queue_by_sign(sign):
    """
    获取给定签名的验证队列记录
    """
    sets = db.select('user_subscribe_queue'
            , where = 'sign = $sign'
            , vars = locals())
    if sets:
        return sets[0]
    return None

def add_subscribe_queue(uid, email, depart_id, from_url):
    """
    添加给定用户到订阅用户等待确认队列中
    """
    # 生成数字签名
    sign = utils.md5('%s' % email)
    db.query('''
            INSERT INTO user_subscribe_queue SET 
                    sign = $sign,
                    uid = $uid,
                    email = $email,
                    depart_id = $depart_id,
                    from_url = $from_url,
                    date_created = NOW(),
                    status = 0
            ON DUPLICATE KEY UPDATE email = $email
                        , depart_id = $depart_id
                        , from_url = $from_url;
            '''
            , vars = locals())
    # 返回签名
    # 用于邮件中的地址
    return sign

def related_subscribed(email, uid):
    """
    将指定email的订阅记录于给定uid关联
    1、若email已经与某用户关联，关联失败
    2、若uid已经与某email关联，关联失败
    """
    # 获取给定email是否于用户关联
    rid = get_subscribed_uid_by_email(email)
    if rid > 0:
        # 给定email已经与用户内码关联
        # 直接退出，关联失败
        return False
    # email没有和任何用户关联
    # 这里进行一次关联
    return db.update('user_subscribe'
            , uid = uid
            , where = 'email = $email'
            , vars = locals())

def get_subscribed_uid_by_email(email):
    """
    用户与给定email关联的用户内码
    """
    sets = db.select('user_subscribe'
            , what = 'uid'
            , where = 'email = $email'
            , vars = locals())
    if sets:
        return sets[0].uid
    return None

def subscribed_by_email(email):
    """
    给定email是否已完成订阅
    """
    sets = db.query('''
            SELECT EXISTS(SELECT NULL 
                          FROM user_subscribe 
                          WHERE email = $email) AS ext;
            '''
            , vars = locals())
    if sets:
        return sets[0].ext == 1
    return False

def subscribed_by_uid(uid):
    """
    给定用户是否已经订阅
    """
    sets = db.query('''SELECT EXISTS(SELECT NULL 
                                     FROM user_subscribe 
                                     WHERE uid = $uid) AS ext;'''
            , vars = locals())
    if sets:
        return sets[0].ext == 1
    return False

def add_subscribe(uid, email, depart_id):
    """
    添加订阅
    @uid as bigint, 用户内码
    @email as string, 邮箱地址
    @province as int, 所在省市
    """
    return db.query('''
            INSERT INTO user_subscribe SET 
                    id = $uid,
                    email = $email,
                    depart_id = $depart_id,
                    date_created = NOW()
            ON DUPLICATE KEY UPDATE email = $email, depart_id = $depart_id;
            '''
            , vars = locals())

def get_user_bind_by_id(bind_id, bind_type):
    """
    获取给定用户信息
    """
    sets = db.select('user_bind'
            , where = 'bind_type = $bind_type AND bind_id = $bind_id'
            , vars = locals())
    if sets:
        return sets[0]
    return None

# @id 用户在猫克斯得uid
# @wbusr 通过微博接口 users/show.json 获取到得用户对象
def update_user_from_weibo_callback(id, bind_usr, bind_type, token):
    '微博回调时，更新用户'
    if bind_usr:
        with db.transaction():
            db.update('user_info'
                    , name = bind_usr.screen_name
                    , last_login = web.SQLLiteral('NOW()')
                    , where = 'id = $id'
                    , vars = locals())
            db.update('user_bind'
                    , bind_name = bind_usr.screen_name
                    , bind_avatar = bind_usr.avatar
                    , bind_token = utils.json_encode(token)
                    , where = 'bind_type = $type AND bind_id = $id'
                    , vars = {'type': bind_type, 'id': bind_usr.uid})
    

# @bind_usr 包含绑定的微博用户的对象
# @bind_type 绑定的微博类型
def add_user_from_weibo_callback(bind_usr, bind_type, token):
    '微博回调时，添加用户'
    if bind_usr:
        # 添加一条记录
        with db.transaction():
            uid = db.insert('user_info'
                    , name = bind_usr.screen_name
                    , date_created = web.SQLLiteral('NOW()')
                    , last_login = web.SQLLiteral('NOW()'))
            if uid:
                db.insert('user_bind'
                        , uid = uid
                        , bind_type = bind_type
                        , bind_id = bind_usr.uid
                        , bind_name = bind_usr.screen_name
                        , bind_avatar = bind_usr.avatar
                        , bind_token = utils.json_encode(token))
        if uid:
            # 添加成功
            return storage(id = uid, screen_name = bind_usr.screen_name)
    # 添加失败
    return False

# @bind_id 绑定的微博用户id
# @bind_type 绑定的微博类型
def get_user_by_bind_id(bind_id, bind_type):
    '获取与指定微博绑定的用户信息'
    # 腾讯微博返回的用户id不是整型的
    # 所以这里不能做整型判断及转换
    if not bind_id:
        return False

    # 读取数据库记录
    sets = db.select('user_bind'
            , where = 'bind_type = $bind_type AND bind_id = $bind_id'
            , vars = locals())
    if sets:
        return sets[0]
    return False
    
